﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Catering_Model;
using Catering_DBOpertion;
using System.Data;
using System.Data.SqlClient;

namespace Catering_Operation
{
    public class SMS_MsgDAL:SMS_MsgInfo
    {

        /// <summary>
        /// 查询多条短消息
        /// </summary>
        /// <param name="senderID">发信息人ID</param>
        /// <param name="isDel">是否显示已经删除数据</param>
        /// <returns></returns>
        public List<SMS_MsgInfo> GetAll(string senderID, string isDel)
        {
            string sql = @"SELECT *  FROM [CMS_Catering].[dbo].[SMS_Msg] where senderID = @i and isDel = @d";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",senderID),
                new SqlParameter("@d",isDel)
            };
            DataTable dt = DbHelperSQL.Query(sql, parm).Tables[0];
            List<SMS_MsgInfo> listInfo = new List<SMS_MsgInfo>();
            Catering_Operation.user userRules = new user();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SMS_MsgInfo temp = new SMS_MsgInfo();
                temp.Id = Int32.Parse(dt.Rows[i]["id"].ToString());
                temp.Sender = userRules.GetModel(Int32.Parse(dt.Rows[i]["senderID"].ToString()));
                temp.CText = dt.Rows[i]["cText"].ToString();
                temp.MesType = Int32.Parse(dt.Rows[i]["mesType"].ToString());
                temp.SendTime = DateTime.Parse(dt.Rows[i]["sendTime"].ToString());
                listInfo.Add(temp);
            }
            return listInfo;
        }

        /// <summary>
        /// 查询未读短信
        /// </summary>
        /// <param name="rid">接受人ID</param>
        /// <returns></returns>
        public int GetUNReadCount(string rid)
        {
            string sql = @"SELECT id  FROM [CMS_Catering].[dbo].[SMS_Receiver]  where receiverID=@i and isRead = 0 and isDel=0";
            SqlParameter[] parm = new SqlParameter[] {
                new SqlParameter("@i",rid)
            };
            return DbHelperSQL.Query(sql, parm).Tables[0].Rows.Count;
        }

        public DataRow GetInBox(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT *  FROM [CMS_Catering].[dbo].[View_Of_InBox] where 1=1 ");
            sql.Append(string.Format(" and rid ={0}", id));
            return DbHelperSQL.Query(sql.ToString()).Tables[0].Rows[0];
        }

        public DataRow GetOutBox(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from View_Of_OutBox where 1=1  ");
            sql.Append(string.Format(" and id={0}", id));
            return DbHelperSQL.Query(sql.ToString()).Tables[0].Rows[0];
        }
        /// <summary>
        /// 查询单条信息
        /// </summary>
        /// <param name="id">信息id</param>
        /// <param name="table">选择收件箱或发件箱</param>
        /// <returns></returns>
        public DataTable GetSingleMes(string id, string table)
        {
            SMS_MsgInfo model = new SMS_MsgInfo();
            string sql = "select * from " + table + " where id=@i";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",id)
            };
            return DbHelperSQL.Query(sql, parm).Tables[0];
        }

        /// <summary>
        /// 将收件箱信息修改为已读
        /// </summary>
        /// <param name="id">信息ID</param>
        public void updateRead(string id)
        {
            string sql = "update SMS_Receiver set isRead = 1 where id=@i";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",id)
            };
            DbHelperSQL.ExecuteSql(sql, parm);
        }

        /// <summary>
        /// 删除已发送信息
        /// </summary>
        /// <param name="id">信息ID</param>
        /// <returns></returns>
        public int doDelInBox(string id)
        {
            string sql = @"UPDATE [CMS_Catering].[dbo].[SMS_Msg]
   SET [isDel] = 1
 WHERE id=@i";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",id)
            };
            return DbHelperSQL.ExecuteSql(sql, parm);
        }

        /// <summary>
        /// 删除收件箱信息
        /// </summary>
        /// <param name="id">信息ID</param>
        /// <returns></returns>
        public int doDelOutBox(string id)
        {
            string sql = @"UPDATE [CMS_Catering].[dbo].[SMS_Receiver]
   SET [isDel] = 1 WHERE id=@i";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",id)
            };
            return DbHelperSQL.ExecuteSql(sql, parm);
        }

        /// <summary>
        /// 收件箱
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="isDel">是否删除</param>
        /// <returns></returns>
        public DataTable GetInBoxByUserID(string userID, string isDel)
        {
            string sql = @"SELECT *
  FROM [CMS_Catering].[dbo].[View_Of_InBox] where receiverID =@i and isDel=@d order by sendTime desc";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",userID),
                new SqlParameter("@d",isDel)
            };
            return DbHelperSQL.Query(sql, parm).Tables[0];
        }

        /// <summary>
        /// 查询多条短消息,已发消息
        /// </summary>
        /// <param name="senderID">发信息人ID</param>
        /// <param name="isDel">是否显示已经删除数据</param>
        /// <returns></returns>
        public DataTable GetOutBox(string senderID, string isDel)
        {
            string sql = @"SELECT *  FROM [CMS_Catering].[dbo].[View_Of_OutBox] where senderID = @i and isDel = @d";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@i",senderID),
                new SqlParameter("@d",isDel)
            };
            return DbHelperSQL.Query(sql, parm).Tables[0];
        }

        public int DelSMSByID(int id)
        {
            string sql = @"update CMS_Catering set isDel=1 WHERE id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
            return DbHelperSQL.ExecuteSql(sql, parm);
        }

        /// <summary>
        /// 查询一条短信息
        /// </summary>
        /// <param name="id">短信息ID</param>
        /// <returns></returns>
        public SMS_MsgInfo GetSMSByID(int id)
        {
            string sql = @"SELECT *  FROM [CMS_Catering].[dbo].[SMS_Msg] where id = @id";
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@id",id)
            };
            DataTable dt = DbHelperSQL.Query(sql, parm).Tables[0];
            SMS_MsgInfo model = new SMS_MsgInfo();
            model.Id = id;
            model.CText = dt.Rows[0]["cText"].ToString();
            model.MesType = Convert.ToInt32(dt.Rows[0]["mesType"]);
            Catering_Operation.user userRules = new user();
            model.Sender = userRules.GetModel(Int32.Parse(dt.Rows[0]["senderID"].ToString()));
            model.SendTime = DateTime.Parse(dt.Rows[0]["sendTime"].ToString());
            return model;
        }
        /// <summary>
        /// 添加短信息
        /// </summary>
        /// <param name="smsInfo">短信息内容</param>
        /// <param name="receiverIDs">接受人ID</param>
        /// <returns></returns>
        public int addsms(SMS_MsgInfo smsInfo, string[] receiverIDs)
        {
            try
            {
                int num = 0;
                string senderSql = @"INSERT INTO [CMS_Catering].[dbo].[SMS_Msg]
           ([senderID]
           ,[cText]
           ,[mesType],[isDel])
     VALUES(@s,@c,@t,0)";
                SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@s",smsInfo.Sender.id),
                new SqlParameter("@c",smsInfo.CText),
                new SqlParameter("@t",smsInfo.MesType)
            };
                num = DbHelperSQL.ExecuteSql(senderSql, parm);
                for (int i = 0; i < receiverIDs.Count(); i++)
                {
                    string receiverSql = @"INSERT INTO [CMS_Catering].[dbo].[SMS_Receiver]
           ([msgID]
           ,[receiverID]
           ,[mesType]
           ,[isRead],[isDel])
     VALUES
           (@i,@r,@m,0,0)";
                    parm = new SqlParameter[] { 
                    new SqlParameter("@i",DbHelperSQL.GetMaxID("id","SMS_Msg") - 1),
                    new SqlParameter("@r",receiverIDs[i]),
                    new SqlParameter("@m",smsInfo.MesType)
                };
                    DbHelperSQL.ExecuteSql(receiverSql, parm);
                }
                return num;
            }
            catch (Exception)
            {
                return 0;
            }
        }
    }
}
